-- @author fangpeiyun
-- @date 2023.03.01
-- 存储健康分仅仅针对2MB以上的表
-- 2023.08.19 【已下线】

use hdp_lbg_supin_zplisting;

insert overwrite table app_rock_health_score_day partition (dt=${dateSuffix})
select 
    concat(a.db_name,'.',a.tbl_name) as table_name,
    case
        -- 针对2GB以下的表直接给100分 2147483648=2*1024*1024*1024
        when a.filesize < 2147483648 then 100

        -- 表已经做了极限存储或人工审核过，也直接100分
        -- when c.is_managed then 100

        -- 产出时间距现在还不足10天的表，还未积累足够的访问情况信息，无法合理配置生命周期，会先给99分
        when a.produce_time > '${#date(0, 0, -10):yyyy-MM-dd#}' then 99

        -- 30天内有访问过的非分区表，认为是有保留的必要的，给100分
        when a.is_partition = 0 and a.access_last_time >= '${#date(0, 0, -30):yyyy-MM-dd#}' then 100

        -- 健康分按照建议保留的天数和实际保留的天数,只要配置生命周期给20分
        when a.is_partition = 1 and a.lifecycle <> 0 then
            least(100-if(a.lifecycle-a.recommend_lifecycle>80, 80, a.lifecycle-a.recommend_lifecycle),100)
        -- 分区表未设置生命周期或非分区表30天无访问，直接给10分
        else 10
    end as health_score,
    case 
        when a.user_name = '' or a.user_name is null then 'unknown'
        else a.user_name
    end as user_name,
    a.filesize,
    case
        when b.org_cn_name = '' or b.org_cn_name is null then 'unknown'
        else b.org_cn_name
    end as org_cn_name,
    a.lifecycle as config_lifecycle,
    a.recommend_lifecycle,
    case
        when a.is_partition = 0 and a.access_last_time >= '${#date(0, 0, 30):yyyy-MM-dd#}' then 1
        else 0
    end as is_in_month_visit_non_part,
    case
        when a.produce_time > '${#date(0, 0, -10):yyyy-MM-dd#}' then 1
        else 0
    end as is_produce_time_in_ten_day,
    a.is_partition,
    a.max_access_span_in_100_days as max_access_day_in_three_month
from (
    select
        db_name,
        tbl_name,
        tbl_type,
        user_name,
        location,
        is_partition,
        dt_partition,
        lifecycle,
        hot,
        filesize,
        is_claim,
        security_level,
        importance_level,
        access_last_time,
        update_last_time,
        status,
        create_time,
        table_id,
        produce_time,
        case
            when max_access_span_in_100_days is NULL then 0
            else max_access_span_in_100_days
        end as max_access_span_in_100_days,
        case
            when max_access_span_in_100_days <= 4 or max_access_span_in_100_days is NULL then 7
            when max_access_span_in_100_days <= 12 then 15
            when max_access_span_in_100_days <= 30 then 33
            when max_access_span_in_100_days <= 90 then 93
            when max_access_span_in_100_days <= 180 then 183
            when max_access_span_in_100_days <= 365 then 368
            when max_access_span_in_100_days <= 731 then 1095
            else -1
        end as recommend_lifecycle
    from hdp_lbg_supin_zplisting.dwd_zp_rock_table_meta_info
    where dt = '${#date(0, 0, -1):yyyyMMdd#}'
        and filesize > 2*1024*1024*1024
) a
left join (
    select
        username,
        realname as user_name_cn,
        concat(
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-4],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-3],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-2],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-1]
        ) as org_cn_name
    from hdp_lbg_supin_zplisting.dim_zp_n_n_emp_org
    where dt = '${#date(0, 0, -1):yyyyMMdd#}'
) b on a.user_name = b.username;